/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package user;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author northpole
 */
public class Connector {

    private Connection con;

    public Connector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/socialSite", "northpole", "northy");
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    protected void finalize() {

        if (con != null) {
            try {
              con.close();
            } catch (SQLException ex) {
                Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

    public String athenticate(String username, String password) {
        int i = 0;
        String result = null;
        try {
            String query = "SELECT username FROM user where username=? AND password=?";
            if(con!=null)
            {
                PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username);
            stmt.setString(2, password);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                i++;
                result = rs.getString("username");
            }
            }
            else
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (i == 1) {
            return result;
        } else {
            return null;
        }
    }

    ArrayList getFriendId(int id) {

        int i = 0;
        ArrayList result = new ArrayList();
        String query = "SELECT user_iduser1,user_iduser  FROM user_has_friend "
                + "WHERE (user_iduser=? OR user_iduser1=?)"
                + " AND friendReq='0'";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, id);
            stmt.setInt(2, id);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(rs.getString("user_iduser1"));
                result.add(rs.getString("user_iduser"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    String[] getInfo(String username) {
        String[] results = new String[5];
        int i = 0,id=-1;
        String query = "SELECT `iduser`,`name`,`surname`,`dateOfBirth`,`welcomeText` FROM `user` WHERE username=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                id=rs.getInt("iduser");
                results[0] = rs.getString("name");
                results[1] = rs.getString("surname");
                results[3] = rs.getString("dateOfBirth");
                results[4] = rs.getString("welcomeText");
            }

            ArrayList interests=this.getInterests(this.getId(username));
            if(interests!=null&&!interests.isEmpty())
            {results[2]=interests.get(0)+",";
             if(interests.size()>1)
             for (int j = 1; j < interests.size(); j++)
            {results[2]=results[2]+interests.get(j)+",";}
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return results;
    }


   ArrayList getInterests(int id){
            String query1 = "SELECT interest FROM interest WHERE idinterest IN";
            String query2 = "SELECT interest_idinterest FROM user_has_interest WHERE user_iduser=?";
            query1= query1+"("+query2+")";

            ArrayList interests = new ArrayList();
        try {
            PreparedStatement stmt = con.prepareStatement(query2);
             ResultSet rs;int i = 0;
            /*stmt.setInt(1, id);
            int id1 = -1;

           rs = stmt.executeQuery();
            while (rs.next()) {
                id1 = rs.getInt("interest_idinterest");
                i++;
            }*/
            stmt = con.prepareStatement(query1);
            stmt.setInt(1, id);
            int id2;
            i = 0;
            rs = stmt.executeQuery();
            while (rs.next()) {
                interests.add(rs.getString("interest"));
                i++;
            }

            System.out.println(query1+id+"salala"+interests.toString());
            return interests;
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

    return interests;
    }

    int getId(String username) {
        int i = 0;
        int result = -1;
        try {

            String query = "SELECT iduser FROM user where username=?";
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username);
            // stmt.setString(2, password);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                i++;
                result = rs.getInt("iduser");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (i == 1) {
            return result;
        } else {
            return -1;
        }
    }

    String[] getPublicInfo(String username) {
        String[] results = new String[2];
        int i = 0;
        String query = "SELECT `welcomeText` FROM `user` WHERE username=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                results[0] = rs.getString("welcomeText");
            }

            ArrayList interests=this.getInterests(this.getId(username));
            if(interests!=null&&!interests.isEmpty())
            {results[1]=interests.get(0)+",";
             if(interests.size()>1)
             for (int j = 1; j < interests.size(); j++)
                if(interests.size()>1)
                {results[1]=results[1]+interests.get(j)+",";}
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return results;
    }
    //info that is public for friends

    String[] getFriendsPublicInfo(String username) {
        String[] results = new String[5];
        int i = 0;
        String query = "SELECT `name`,`surname`,`dateOfBirth`,`welcomeText` FROM `user` WHERE username=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                results[0] = rs.getString("name");
                results[1] = rs.getString("surname");
                results[2] = rs.getString("dateOfBirth");
                results[4] = rs.getString("welcomeText");
            }

            ArrayList interests=this.getInterests(this.getId(username));
            if(interests!=null&&!interests.isEmpty())
            {results[3]=interests.get(0)+",";
             if(interests.size()>1)
             for (int j = 1; i < interests.size(); j++)
            {results[3]=results[3]+interests.get(j)+",";}
            }
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return results;
    }

    int acceptFriend(int id1, String username0) {
        int results;
        int id2 = 0;
        int i = 0;
        String query1 = "SELECT `iduser` FROM `user` WHERE username=?";
        String query = "UPDATE `socialSite`.`user_has_friend` SET `friendReq` = '0' WHERE "
                + "(`user_has_friend`.`user_iduser` =? AND `user_has_friend`.`user_iduser1` =?)";
        /* OR "
        + "(`user_has_friend`.`user_iduser1` =? AND `user_has_friend`.`user_iduser` =?);";*/
        try {
            PreparedStatement stmt = con.prepareStatement(query1);
            stmt.setString(1, username0);
            ResultSet rs, rs1;
            rs = stmt.executeQuery();

            while (rs.next()) {
                id2 = rs.getInt("iduser");

            }

           // System.out.println("\n" + id1 + " asdf " + id2);

            stmt = con.prepareStatement(query);
            stmt.setInt(1, id2);
            stmt.setInt(2, id1);
            //stmt.setInt(4, id2);
            //stmt.setInt(3, id2);

            i = stmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (i != 1) {
            return -1;
        } else {
            return i;
        }
    }

    int addPendingReq(int id1, String friendsUsername) {
        int results;
        int id2 = 0;
        int i = 0;
        if(id1==-1)
            return -1;

        String query1 = "SELECT `iduser`FROM `user` WHERE username=?";
        String query = "INSERT INTO `socialSite`.`user_has_friend` "
                + "(`user_iduser` ,`user_iduser1` ,`friendReq` )VALUES (?, ?, '1')";

        String query2="SELECT * FROM user_has_friend WHERE "
                + "(user_iduser=? AND user_iduser1=?) OR (user_iduser=? AND user_iduser1=?)";

        try {
            PreparedStatement stmt = con.prepareStatement(query1);
            stmt.setString(1, friendsUsername);
            ResultSet rs, rs1;
            rs = stmt.executeQuery();

            while (rs.next()) {
                id2 = rs.getInt("iduser");
                i++;

            }
           // System.out.println("id1=="+id1+" id2=="+id2);
            stmt=con.prepareStatement(query2);
            stmt.setInt(1,id1);
            stmt.setInt(2, id2);
            stmt.setInt(3, id1);
            stmt.setInt(4, id2);
            rs=stmt.executeQuery();
            //System.out.println(stmt.toString());
            i=0;
            while(rs.next())
            {i++;}
            if(i!=0)
                return -1;
            //System.out.println(i);
            stmt = con.prepareStatement(query);
            stmt.setInt(2, id2);
            stmt.setInt(1, id1);
            i = stmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (i != 1) {
            return -1;
        } else {
            return i;
        }
    }

    ArrayList loadFriendRequests(int id) {
        int i = 0;
        ArrayList result = new ArrayList();
        String query = "SELECT user_iduser1  FROM user_has_friend where user_iduser=? AND friendReq='1'";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, id);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(rs.getString("user_iduser1"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    ArrayList getFriendUsernames(ArrayList ids) {
        if (ids == null) {
            return null;
        }
        int i = 0;
        ArrayList result = new ArrayList();
        String query = "SELECT username  FROM user where iduser=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            for (int j = 0; j < ids.size(); j++) {
                int x = Integer.parseInt(ids.get(j).toString());
                stmt.setInt(1, x);
                ResultSet rs;
                rs = stmt.executeQuery();

                while (rs.next()) {
                    result.add(rs.getString("username"));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public ArrayList getFriendIds(int id) {
        ArrayList result = new ArrayList();
        ArrayList uniqueResults = null;
        String query = "SELECT user_iduser,"
                            + "user_iduser1"
                + " FROM user_has_friend"
                + " WHERE (user_iduser=? OR user_iduser1=?) AND friendReq=0";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, id);
            stmt.setInt(2, id);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(rs.getString("user_iduser1"));
                result.add(rs.getString("user_iduser"));
            }
            for (int i = 0; i < result.size(); i++) {
                if (result.get(i).equals(id)) {
                    result.remove(i);
                }


                uniqueResults = new ArrayList(new HashSet(result));

                /* System.out.print("unique== "+uniqueResults);
                System.out.print("results== "+result);
                 */            }
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        result = uniqueResults;



        return result;
    }

    public ArrayList suggestUsernames(String username, int id) { //suggest friends of friends
        ArrayList ids = this.getFriendId(id);
        ArrayList result = new ArrayList();
        ArrayList usernames = new ArrayList();
        String query = "SELECT iduser from user,user_has_friend "
                + "WHERE user_has_friend.user_iduser!=user.iduser "
                + "AND user_has_friend.user_iduser1!=user.iduser"
                + " AND iduser=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, id);
            ResultSet rs;
            rs = stmt.executeQuery();
            while (rs.next()) {
                result.add(rs.getString("iduser"));
            }

            usernames = this.getFriendUsernames(result);
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return usernames;
    }

    public int SignUp(User user) {
        int result = -1;
        String sth = null;
        String query = "INSERT INTO `socialSite`.`user` ("
                + "`username` ,`password` ,`name` ,`surname` ,`dateOfBirth`,`welcomeText` )"
                + "VALUES (?,?,?,?,?,?)";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, user.getUsername().toString());
            stmt.setString(2, user.getPassword().toString());
            stmt.setString(3, user.getName().toString());
            stmt.setString(4, user.getSurname().toString());
            // DateFormat df = new SimpleDateFormat();

            //   Date date = null;
            //     try {
            //        date =  df.parse(user.getDateOfBirth().toString());
            //     } catch (ParseException ex) {
            //        Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
            //     }

            stmt.setString(5, user.getDateOfBirth().toString());
            stmt.setString(6, user.getWelcomeText().toString());

          //  sth = stmt.toString();
           // System.out.println("AAAAAAAAAAAAAAAAAAAAAAAAAA " + sth);

            result = stmt.executeUpdate();
            if(result==1)
            {int intsres;
                String[] interests = user.getInterests().split(",");
            for(int j=0;j<interests.length;j++)
            {intsres=this.addInterest(this.getId(user.getUsername()), interests[j]);}
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
            System.out.print(sth);
        }

        return result;//result;

    }

/**
 *
 * adds a new user has interest relation if given interest exists
*else it adds both a new interest and a new relation
*MUST return 1 or the number of the affected rows
 * result is initialized at -1;
**/
   public int addInterest(int idUser,String interest){

            int result = -1;
            ArrayList<Integer> selectResult = new ArrayList();
            ArrayList<String> ints = new ArrayList();
            ArrayList<String> noMatch = new ArrayList();
            ResultSet rs;
            int noMatchInters = 0;
            String intExists = "INSERT INTO `socialSite`.`user_has_interest`" + " (`user_iduser` ,`interest_idinterest`)VALUES (?, ?)";
            String intNoExist = "INSERT INTO `socialSite`.`interest` " + "(`idinterest` ,`interest`)VALUES (NULL , ?)";
            String querySel = "Select `idinterest` FROM interest WHERE interest=?";
            String querySel2="SELECT interest_idinterest,user_iduser FROM user_has_interest "
                    + "WHERE user_iduser=? AND interest_idinterest=?";
            try {
            PreparedStatement stmt = con.prepareStatement(querySel);
            stmt.setString(1, interest);
            rs=stmt.executeQuery();
            while(rs.next()){
            selectResult.add(rs.getInt("idinterest"));
            }
            if(selectResult.isEmpty())
            {//if we didn't find a matching interest we make one and make a new user-interest relationship
                stmt=con.prepareStatement(intNoExist);
             stmt.setString(1, interest);
            result=stmt.executeUpdate();
            stmt = con.prepareStatement(querySel);
            stmt.setString(1, interest);
            rs=stmt.executeQuery();
            while(rs.next()){
            selectResult.add(rs.getInt("idinterest"));
            }
            stmt=con.prepareStatement(intExists);



             stmt.setInt(1, idUser);
             stmt.setInt(2, selectResult.get(0));
             System.out.println(stmt.toString());
            result=stmt.executeUpdate();
            }
            else{//either interest exists and relationship doensn't or both exist
            stmt=con.prepareStatement(querySel2);
            int i=0;
            stmt.setInt(1, idUser);
            stmt.setInt(2,selectResult.get(0));
             rs=stmt.executeQuery();
            while(rs.next())
            {i++;}
            if(i>0)//they both exist
            {}//do nothing
            else//interest exists but not the relation
                {
                   stmt=con.prepareStatement(intExists);



             stmt.setInt(1, idUser);
             stmt.setInt(2, selectResult.get(0));
             System.out.println(stmt.toString());
            result=stmt.executeUpdate();  }
                }
            {}



            return result;
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
    return result;}







    String findUsername(String username) {
        int i = 0;
        String result = null;
        String query = "SELECT username FROM user WHERE username=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                i++;
                result = rs.getString("username");
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (i > 0) {
            return "Error";
        } else {
            return result;
        }
    }

    int updateProfile(User user) {
        int result = -1;
        String sth = null;

        String query = "UPDATE  `socialSite`.`user` SET  "
                + "`name` = ?,`surname` = ?, `dateOfBirth`=?,"
                +"`welcomeText` =?WHERE  `user`.`iduser` =?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, user.getName().toString());
            stmt.setString(2, user.getSurname().toString());
            DateFormat df = new SimpleDateFormat();


            stmt.setString(3, user.getDateOfBirth());
            stmt.setString(4, user.getWelcomeText().toString());
            stmt.setInt(5, user.getId());
            // sth=stmt.toString();
            result = stmt.executeUpdate();

            if(result==1)
            {int intsres;
                String[] interests = user.getInterests().split(",");
            for(int j=0;j<interests.length;j++)
            {intsres=this.addInterest(this.getId(user.getUsername()), interests[j]);}
            }

            return result;
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
            System.out.print(sth);
        }

        return result;//result;


    }

    void deleteFriend(int id, String friendUsername) {
        int result = -1;
        String query = "DELETE FROM user_has_friend WHERE"
                + " (user_iduser = ? AND user_iduser1 = ?)"
                + "OR (user_iduser1=? AND user_iduser=?)";
        int fid = this.getId(friendUsername);
        try {System.out.println("DELETE"+id+fid);
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, id);
            stmt.setInt(2, fid);
            stmt.setInt(3, id);
            stmt.setInt(4, fid);
            result = stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);

        }
    }

    void deleteUser(User user) {
        int result = -1;
        String query = "DELETE FROM `socialSite`.`user` WHERE `user`.`username` = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, user.getUsername());
            result = stmt.executeUpdate();
            System.out.println(stmt.toString());
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);

        }
    }
    //id1 is the id of the logged in user who makes the request while username2 is the username of the one to whom the request is made

    boolean reqExists(int id1, String username2) {//vres:
                        /*  to id2 pou adistoixei sto username2
         * to pending req anamesa sto id2 kai to id1
         * an epistrafoun 0 apotelesmata h epistrafei 1 ws apotelesma dld
         * an yparxei pending req (prepei na einai 1)
         * return true
         */
        boolean result = false;
        int i = 0;
        int id2 = -1;
        String query = "SELECT id FROM user WHERE username=?";
        String query2 = "SELECT pendingReq,user_iduser1,user_iduser2 FROM "
                + "user_has_friend WHERE ((user_iduser1=? AND user_iduser2=? )OR"
                + " (user_iduser1=? AND user_iduser2=?))"
                + "AND pendingReq=1 ";
        //throw new UnsupportedOperationException("Not yet implemented");
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, username2);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                i++;
                id2 = rs.getInt("id");
            }

            if (i != 1) {
                return false;
            } else {
                stmt = con.prepareStatement(query2);
            }
            stmt.setInt(1, id1);
            stmt.setInt(2, id2);
            stmt.setInt(3, id2);
            stmt.setInt(4, id1);
            rs = stmt.executeQuery();
            i = 0;
            while (rs.next()) {
                i++;
            }
            if (i == 1) {
                return true;
            } else if (i == 0) {
                return false;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    void rejectReq(int id, String username2) {
        //throw new UnsupportedOperationException("Not yet implemented");
   int result = -1,id2=-1,i=0;
        String query = "DELETE FROM `socialSite`.`user_has_friend` WHERE "
                +"((user_iduser1=? AND user_iduser2=? )OR"
                + " (user_iduser1=? AND user_iduser2=?))"
                + "AND pendingReq=1 ";
       String query2="SELECT id FROM user WHERE username=?";
        try {
            PreparedStatement stmt = con.prepareStatement(query2);
            stmt.setString(1, username2);
            ResultSet rs;
            rs = stmt.executeQuery();

            while (rs.next()) {
                i++;
                id2 = rs.getInt("id");
            }
            if(id2!=-1)
            {
                stmt = con.prepareStatement(query);
                stmt.setInt(1, id);
                stmt.setInt(2, id2);
                stmt.setInt(3, id2);
                stmt.setInt(4, id);

            }
            result = stmt.executeUpdate();
            //System.out.println(stmt.toString());
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);

        }


    }

    public ArrayList getSentPms(int Id){

            ArrayList <Pm>result = new ArrayList();
            Pm temp;
            String query = "Select pmsId,receiver,content"
                    + " from pms where sender=?;";
             try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, Id);
            ResultSet rs;
            rs = stmt.executeQuery();
            while (rs.next()) {
                temp=new Pm();
                temp.id=rs.getInt(1);
                temp.from=Id;
                temp.to=rs.getInt("receiver");
                temp.text=(rs.getString("content"));
                result.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
             return result;
    }



    public ArrayList getUnreadPms(int Id){

             ArrayList <Pm>result = new ArrayList();
            Pm temp;
            String query = "Select `pmsId`,`content`,sender FROM pms where `receiver`=? AND `read`=0";
             try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, Id);
            ResultSet rs;
            rs = stmt.executeQuery();
            while (rs.next()) {
                temp=new Pm();
               temp.id=rs.getInt(1);
                temp.from=rs.getInt("sender");
                temp.to=Id;
                temp.text=(rs.getString("content"));
                result.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
             return result;
    }

        public ArrayList getReceivedPms(int Id){
             ArrayList <Pm>result = new ArrayList();
            Pm temp;
            String query = "SELECT pmsId,content,sender"
                    + " FROM pms WHERE receiver=?";

             try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, Id);
            ResultSet rs;
            rs = stmt.executeQuery();

           // System.out.println(stmt.toString());
            while (rs.next()) {
                 temp=new Pm();
               temp.from=rs.getInt("sender");
                temp.to=Id;
                temp.text=(rs.getString("content"));
                 temp.id=rs.getInt(1);
                result.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
             return result;
    }
       public void readPm(Pm pm){
           int result;
       String query="UPDATE `socialSite`.`pms` SET `read` = '1'"
               + " WHERE `pms`.`pmsId` =?;";
                  try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setInt(1, pm.id);

           // System.out.println(stmt.toString());
           result=stmt.executeUpdate();

           //System.out.println(result);

                  } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
       }

       public void createPm(Pm pm){
       int result;int id1 = -1,id2 = -1;
           String query="INSERT INTO `socialSite`.`pms` "
                   + "(`content`, `pmsId`, `friendshipId`, `user_has_friend_user_iduser`, "
                   + "`user_has_friend_user_iduser1`, `user_has_friend_friendshipId`, `read`, `sender`, `receiver`)"
                   + " VALUES (?, NULL, '-1', ?, ?, '-1', 0, ?, ?)";

           String query1="Select user_iduser,user_iduser1"
                   + " from user_has_friend "
                   + "where (user_iduser=? AND user_iduser1=?) OR (user_iduser1=? AND user_iduser=?)";

         try {
            PreparedStatement stmt = con.prepareStatement(query1);
            stmt.setInt(1, pm.from);
            stmt.setInt(3, pm.from);
            stmt.setInt(2, pm.to);
            stmt.setInt(4, pm.to);
            ResultSet rs;
            rs = stmt.executeQuery();
            while (rs.next()) {
             id1=rs.getInt("user_iduser");
            id2=rs.getInt("user_iduser1");}

             stmt = con.prepareStatement(query);

             if(id1==-1||id2==-1)
                 return;
           //  System.out.println(stmt.toString());
             stmt.setString(1, pm.text);
             stmt.setInt(2, id1);
             stmt.setInt(3, id2);
             stmt.setInt(4, pm.from);
             stmt.setInt(5, pm.to);


           result=stmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }


       }

     public ArrayList getSuggestions(int Id){
         int x = -1;
         ArrayList <Suggestion> result=new ArrayList();
         Suggestion y;
     String query="Select * from suggestion where idsuggestion="
             + "(SELECT suggestion_idsuggestion from user_has_suggestion where user_iduser=?)";
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            ResultSet rs;
           stmt=con.prepareStatement(query);
           stmt.setInt(1, x);
           rs=stmt.executeQuery();
           while(rs.next()){
           y=new Suggestion();
                   y.commend=rs.getString("comment");
                   y.url=rs.getString("url");
           result.add(y);
           }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

     return result;
     }

    ArrayList getPendingFriendRequestsTo(String username) {
            String query = "SELECT user_iduser FROM user_has_friend WHERE user_iduser1=? AND friendReq=1";
            ArrayList ids = new ArrayList();
            ArrayList usernames=new ArrayList();
            try {
            PreparedStatement stmt = con.prepareStatement(query);
            int id = this.getId(username);
            stmt.setInt(1, id);
            ResultSet rs;
            rs=stmt.executeQuery();
            while(rs.next()){
            ids.add(rs.getInt("user_iduser"));
            }
            usernames=this.getFriendUsernames(ids);
            } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return usernames;
    }


    ArrayList search(String username,
            String name,
            String surname,
            String dateOfBirth,
            String interests,
            String logicClause,
            int noFOps){

       System.out.println(interests);
        int args=0;
        String query="SELECT username FROM user WHERE ";
        String superQuery;

        String []ints=new String[0];
        if(interests!=null&&!interests.isEmpty())
        ints=interests.split(",");

        ArrayList result=new ArrayList();
         String[] ops = new String[noFOps];
         logicClause=" "+logicClause+" ";

    if(username!=null){ops[args]=username;query = query + "username LIKE ?";args++;}
    if(name!=null){ops[args]=name;if(args>0){query=query+logicClause;}query = query + "name LIKE ?";args++;}
    if(surname!=null){ops[args]=surname;if(args>0){query=query+logicClause;}query=query+"surname LIKE ?";args++;}
    if(dateOfBirth!=null){ops[args]=dateOfBirth;if(args>0){query=query+logicClause;}query=query+"dateOfBirth LIKE ?";args++;}

         if(interests!=null)
    {String query2="(SELECT user_iduser "
             + " FROM interest, user_has_interest WHERE interest LIKE ";

             if(ints.length==1)
             {
                 query2 = query2 + " ?  ";}

             else if(ints.length>=2)
             {
                 for (int i = 1; i < ints.length - 1; i++)
         query2=query2+" ?  OR interest LIKE";
     query2=query2+"?";}
     query2=query2+"";
     String query2sup= "AND idinterest = interest_idinterest)";
     if(args>0)
     superQuery=query+" "+logicClause +" iduser IN" +query2+query2sup;
     else
         superQuery=query+" iduser IN" +query2+query2sup;
         }
 else
     superQuery=query;


        try {
            PreparedStatement stmt = con.prepareStatement(superQuery);

            for(int i=1;i<=noFOps;i++)
            stmt.setString(i,"%"+ ops[i-1]+"%");


            for(int i=0;i<ints.length;i++){
            stmt.setString(noFOps++,"%"+ ints[i]+"%");

            System.out.println(noFOps+" "+ints.length+" "+i);
            }

            ResultSet rs;
           // System.out.println(superQuery);
           // System.out.println(stmt.toString());
          // result.add(stmt.toString());
           
            rs=stmt.executeQuery();
            while(rs.next())
            {result.add(rs.getString("username"));}
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
         return result;
    }

    ArrayList pendingFriendRequestsFrom(String username) {
        String query = "SELECT user_iduser1 FROM user_has_friend WHERE user_iduser=? AND friendReq=1";
            ArrayList ids = null;
            ArrayList usernames=null;
            try {
            PreparedStatement stmt = con.prepareStatement(query);
            int id = this.getId(username);
            stmt.setInt(1, id);
            ResultSet rs;
            rs=stmt.executeQuery();
            while(rs.next()){
            ids.add(rs.getInt("user_iduser"));
               }
            usernames=this.getFriendUsernames(ids);
            } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return usernames;
    }

    String getUsername(int id) {
        String query = "SELECT username  FROM user where iduser=?";
        String result=null;
        try {
            PreparedStatement stmt = con.prepareStatement(query);

                stmt.setInt(1, id);
                ResultSet rs;
                rs = stmt.executeQuery();

                while (rs.next()) {
                    result=rs.getString("username");
                }

        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    }





 